﻿<%@ WebHandler Language="C#" Class="getDb" %>

using System;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using Newtonsoft.Json;

public class getDb : IHttpHandler
{
    protected static string ConStr = System.Configuration.ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
    public void ProcessRequest(HttpContext context)
    {
        var rs = context.Response;
        var rq = context.Request;
        rs.ContentType = "text/plain";

        string ip = "127.0.0.1";
        string usName = "sa";
        string dbpwd = "1105";
        DataTable dta = dat();
        if (dta.Rows.Count > 0)
        {
            ip = dta.Rows[0]["hostIp"].ToString();
            usName = dta.Rows[0]["DbUser"].ToString();
            dbpwd = dta.Rows[0]["DbPwd"].ToString();
        }

        //查询所在服务器的所有数据库或者非系统库
        if (rq["geDbLst"] != null && rq["ip"] != null && rq["uName"] != null && rq["upwd"] != null)
        {
            ip = rq["ip"].Trim();
            usName = rq["uName"].Trim();
            dbpwd = rq["upwd"].Trim();
            string DbLst = rq["geDbLst"].Trim();
            if (DbLst == "All")
            {
                DataTable dt = new DataTable();
                dt.Columns.Add("DbName", typeof(string));

                System.Collections.ArrayList arrl = SqlHelper.GetAllDataBase(ip, usName, dbpwd);
                foreach (string db in arrl)
                {
                    dt.Rows.Add(db);
                }

                string jsnStr = JsonConvert.SerializeObject(dt);
                jsnStr = "{\"comments\":" + jsnStr + "}";
                rs.Write(jsnStr);
                rs.End();
            }
            else
            {
                string dbStr = "Data Source=" + ip + ";DataBase=master;uid=" + usName + ";pwd=" + dbpwd + "";
                System.Collections.Generic.List<string> lst = SqlHelper.databaseList(dbStr);
                DataTable dt = new DataTable();
                dt.Columns.Add("DbName", typeof(string));
                foreach (string db in lst)
                {
                    dt.Rows.Add(db);
                }

                string jsnStr = JsonConvert.SerializeObject(dt);
                jsnStr = "{\"comments\":" + jsnStr + "}";
                rs.Write(jsnStr);
                rs.End();
            }
        }
        //获取所选数据库中所有的表
        if (rq["ProcName"] == null && rq["DbName"] != null && rq["TbName"] == null && rq["hstIp"] == null  && rq["TbName"] == null  && rq["TbName"] == null)
        {
            string dbn = rq["DbName"].Trim();

            string dbStr = "Data Source=" + ip + ";DataBase="+dbn+";uid=" + usName + ";pwd=" + dbpwd + "";
            System.Collections.Generic.IList<string> tbs = SqlHelper.tblist(dbStr);
            DataTable dt = new DataTable();
            dt.Columns.Add("TbName", typeof(string));
            foreach (string tb in tbs)
            {
                dt.Rows.Add(tb);
            }

            string jsnStr = JsonConvert.SerializeObject(dt);
            jsnStr = "{\"comments\":" + jsnStr + "}";
            rs.Write(jsnStr);
            rs.End();
        }
        //根据表名查询表字段及其附属属性
        if(rq["TbName"] != null && rq["DbName"] != null)
        {
            string tbn = rq["TbName"].Trim();
            string dbn = rq["DbName"].Trim();
            string dbStr = "Data Source=" + ip + ";DataBase="+dbn+";uid=" + usName + ";pwd=" + dbpwd + "";
            SqlConnection con = new SqlConnection(dbStr);
            DataTable dt = new DataTable();
            dt.Columns.Add("clmSn",typeof(int));
            dt.Columns.Add("cloumName",typeof(string));
            dt.Columns.Add("bs",typeof(string));
            dt.Columns.Add("primry",typeof(string));
            dt.Columns.Add("ctype",typeof(string));
            dt.Columns.Add("byteLength",typeof(int));
            dt.Columns.Add("cLength",typeof(int));
            dt.Columns.Add("iNull",typeof(string));
            dt.Columns.Add("deaflt",typeof(string));
            dt.Columns.Add("mark",typeof(string));
            string sql = @"SELECT 
                tbName= case when a.colorder=1 then d.name else '' end,
                descrip= case when a.colorder=1 then isnull(f.value,'') else '' end,
                clmSn= a.colorder,cloumName = a.name,
                bs= case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,
                primry= case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
                SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '√' else '' end,
                ctype = b.name, byteLength = a.length,cLength= COLUMNPROPERTY(a.id,a.name,'PRECISION'),
                xsw = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),iNull= case when a.isnullable=1 then '√'else '' end,
                deaflt= isnull(e.text,''),mark   = isnull(g.[value],'')
                FROM  syscolumns a left join systypes b on a.xusertype=b.xusertype
                inner join sysobjects d on a.id=d.id  and d.xtype='U' and  d.name<>'dtproperties' left join 
                syscomments e on a.cdefault=e.id left join sys.extended_properties   g on 
                a.id=G.major_id and a.colid=g.minor_id  left join sys.extended_properties f on 
                d.id=f.major_id and f.minor_id=0 where d.name='"+tbn+"'order by a.id,a.colorder";
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand(sql, con);
                SqlDataReader dr = cmd.ExecuteReader();//执行命令
                while (dr.Read())
                {
                    dt.Rows.Add(
                        dr["clmSn"].ToString(),dr["cloumName"].ToString(),dr["bs"].ToString(),
                        dr["primry"].ToString(),dr["ctype"].ToString(),dr["byteLength"].ToString(),
                        dr["cLength"].ToString(),dr["iNull"].ToString(),dr["deaflt"].ToString(),
                        dr["mark"].ToString()
                        );
                }
                dr.Close();
            }
            catch (SqlException e)
            {
                //MessageBox.Show(e.Message);  
            }
            finally
            {
                if (con != null && con.State == ConnectionState.Open)
                {
                    con.Dispose();
                }
            }
            string jsnStr = JsonConvert.SerializeObject(dt);
            jsnStr = "{\"total\":32,\"rows\":" + jsnStr + "}";
            rs.Write(jsnStr);
            rs.End();
        }
        //根据存储过程名获取存储过程内容
        if(rq["ProcName"] != null && rq["DbName"] != null)
        {
            string pname = rq["ProcName"].Trim();
            string DbName = rq["DbName"].Trim();
            string dbStr = "Data Source=" + ip + ";DataBase="+DbName+";uid=" + usName + ";pwd=" + dbpwd + "";
            //SqlParameter[] sp = { new SqlParameter("@objname",pname)};
            //DataTable dt = SqlHelper.ExecuteProcDataSet(dbStr,CommandType.StoredProcedure,"sp_helptext",sp).Tables[0];
            //string jsnStr = JsonConvert.SerializeObject(dt);
            string getTxt = "select text from syscomments where id=object_id('" + pname + "')";
            object txt = SqlHelper.ExecuteScalar(dbStr,CommandType.Text,getTxt);
            rs.Write(txt);
            rs.End();
        }

    }
    //获取数据库服务器配置参数
    protected DataTable dat()
    {
        DataSet ds = XMLHelper.rds("App_Data/SystemSetting.xml");
        DataTable dtt = ds.Tables[0];
        return dtt;
    }
    public bool IsReusable
    {
        get
        {
            return false;
        }
    }
}